﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Wicresoft.UnifyShow.Business;
using Microsoft.Practices.Unity;
using System.Text;
using System.Configuration;
using Wicresoft.UnifyShow.BusinessImpl;

namespace Wicresoft.UnifyShow2.Portal.Controllers
{
    [Authorize]
    public class SearchController : BaseController
    {
        IUser_X user_x;
        static string sortitem;
        static string sortorder;
        //
        // GET: /Search/

        protected override void Dispose(bool disposing)
        {
            ((IDisposable)user_x).Dispose();
        }

        [InjectionConstructor]
        public SearchController(IUser_X _user_x)
        {
            user_x = _user_x;
        }

        public ActionResult Index()
        {
            SetRoles(user_x);
            ViewBag.IsConfigAdmin = IsConfigAdmin;
            var USER_X = user_x.GetUser_XByName(User.Identity.Name);
            if (USER_X == null)
                throw new Exception("USER_X is null.");
            ViewBag.UserDept = USER_X.Department;
            ViewBag.UserCompany = USER_X.Company;

            var sites = user_x.GetSiteBranchByID(USER_X.ID);

            var company = USER_X.Company;
            SiteBranchModel t = new SiteBranchModel();
            if (USER_X.Department == "省局机关运维" || USER_X.Department == "中软海晟技术支持组")
            {
                ISiteBranch sitebranchs = new SiteBranch();
                var model = sitebranchs.GetSiteALLBranch().FirstOrDefault(s => s.Name == USER_X.Department);
                if (model != null)
                {
                    sites.Add(model);
                }
                company = USER_X.Department;
            }

            if (sites != null && sites.Count > 0 && sites.FirstOrDefault(s => s.Name == company) != null)
            {
                t = sites.FirstOrDefault(s => s.Name == company);
            }
            return View(t);
            //return View();
        }

        public ActionResult GetGroups(string company,string type)
        {
            JsonCollection c = new JsonCollection();

            if (!string.IsNullOrEmpty(company))
            {
                var list = user_x.GetGroupsByCompany(company, type);
                foreach (var l in list)
                {
                    JsonObject each = new JsonObject();
                    each.MergeProperty("label", new JsonConstant(l.Support_Group_Name));
                    each.MergeProperty("value", new JsonConstant(l.Support_Group_Name));
                    c.AppendObject(each);
                }

            }

            JsonObject empty = new JsonObject();
            empty.MergeProperty("label", new JsonConstant("(清除)"));
            empty.MergeProperty("value", new JsonConstant(""));
            c.AppendObject(empty);

            return RawJson(c);

        }


        public ActionResult GetPeople(string company, string group,string type)
        {
            JsonCollection c = new JsonCollection();

            if (!string.IsNullOrEmpty(company))
            {
                List<SupportGroupCompany> list;
                if (string.IsNullOrEmpty(group)) 
                {
                    list = user_x.GetPeoples(company,type);
                } else 
                {
                    list = user_x.GetPeople(company, group, type);
                }

                foreach (var l in list)
                {
                    JsonObject each = new JsonObject();
                    each.MergeProperty("label", new JsonConstant(l.Full_Name));
                    each.MergeProperty("value", new JsonConstant(l.Login_ID));
                    c.AppendObject(each);
                }

            }

            JsonObject empty = new JsonObject();
            empty.MergeProperty("label", new JsonConstant("(清除)"));
            empty.MergeProperty("value", new JsonConstant(""));
            c.AppendObject(empty);


            return RawJson(c);


        }



        [HttpPost]
        public JsonResult SearchResult(string incidentid, string generalstatus, string requesttype, string requeststatus, string requestpriority, string requestbegintime, string requestendtime, string customercompany, string submittercompany, string submittergroup, string submitter, string assigneecompany, string assigneegroup, string assignee, string subject)
        {
            var USER_X = user_x.GetUser_XByName(User.Identity.Name);
            if (USER_X == null)
                throw new Exception("USER_X is null.");
            var sites = user_x.GetSiteBranchByID(USER_X.ID);
            SiteBranchModel t = new SiteBranchModel();
            if (sites != null && sites.Count > 0 && sites.FirstOrDefault(s => s.Name == USER_X.Company) != null)
            {
                t = sites.FirstOrDefault(s => s.Name == USER_X.Company);
            }
            if (t.Alias != "省局" && string.IsNullOrEmpty(assigneecompany))
            {
                customercompany = t.Name;
                submittercompany = t.Name;
                assigneecompany = t.Name;
            }
            int pageIndex, pageSize;
            demoModel model = new demoModel();
            if (int.TryParse(Request.Form["page"], out pageIndex) == false || int.TryParse(Request.Form["rows"], out pageSize) == false)
            {
                throw new Exception("获取页面信息失败");
            }
            sortitem = Request.Form["sort"];
            sortorder = Request.Form["order"];
            model = GetSearchResultModel(incidentid, generalstatus, requesttype, requeststatus, requestpriority, requestbegintime, requestendtime, customercompany, submittercompany, submittergroup, submitter, assigneecompany, assigneegroup, assignee, subject, sortitem, sortorder);
            if (model.total > 0)
            {
                if (pageIndex > 0 && model.total > (pageSize * (pageIndex - 1)))
                {
                    model.rows = model.rows.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();
                }
                else
                {
                    model.rows = model.rows.ToList();
                }
            }
            else
            {
                List<ToDoModel> rows = new List<ToDoModel>();
                model.rows = rows;
            }
            return Json(model, JsonRequestBehavior.AllowGet);
        }

        public void ExportToExcel(string incidentid, string generalstatus, string requesttype, string requeststatus, string requestpriority, string requestbegintime, string requestendtime, string customercompany, string submittercompany, string submittergroup, string submitter, string assigneecompany, string assigneegroup, string assignee, string subject)
        {
            demoModel model = new demoModel();
            model = GetSearchResultModel(incidentid, generalstatus, requesttype, requeststatus, requestpriority, requestbegintime, requestendtime, customercompany, submittercompany, submittergroup, submitter, assigneecompany, assigneegroup, assignee, subject, sortitem, sortorder);

            // IO用于导出并返回excel文件  
            System.IO.StringWriter strWriter = null;

            //System.Web.UI.HtmlTextWriter htmlWriter = null;

            string filename = "工单报表";

            if (model.total >= 0)
            {
                strWriter = new System.IO.StringWriter();

                strWriter.WriteLine("请求ID\t请求类型\t操作\t摘要\t状态\t优先级\t受派者组\t受派者\t建单人\t建单日期");
                foreach (var row in model.rows)
                {
                    strWriter.WriteLine(row.IDS + "\t" + row.TypeS + "\t" + row.Operation + "\t\"" + row.Description + "\"\t" + row.Status + "\t" + row.Priority + "\t" + row.AssignGroup + "\t" + row.Assigner + "\t" + row.Submitter + "\t" + row.CreateDate);
                }
                strWriter.Close();
                Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(filename) + ".xls");
                //Response.AddHeader("Content-Length", strWriter.ToString().Length.ToString());
                Response.ContentType = "application/ms-excel";
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.Write(strWriter.ToString());
                Response.Flush();
                Response.End();
            }
            //XlsDocument xls = new XlsDocument();
            //xls.FileName = "工单报表";

            //Worksheet sheet = xls.Workbook.Worksheets.AddNamed("工单报表");

            //Cells cells = sheet.Cells;//初始化EXCEL文件表单的单元格

            //XF cellXF = xls.NewXF();
            //cellXF.Font.Bold = true;
            //cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
            //cellXF.PatternBackgroundColor = Colors.Grey;//填充的背景底色

            ////cellXF.PatternColor = Colors.Black;//设定填充线条的颜色


            //cells.Add(1, 1, "请求ID",cellXF);
            //cells.Add(1, 2, "请求类型", cellXF);
            //cells.Add(1, 3, "操作", cellXF);
            //cells.Add(1, 4, "摘要", cellXF);
            //cells.Add(1, 5, "状态", cellXF);
            //cells.Add(1, 6, "优先级", cellXF);
            //cells.Add(1, 7, "受派者组", cellXF);
            //cells.Add(1, 8, "受派者", cellXF);
            //cells.Add(1, 9, "建单人", cellXF);
            //cells.Add(1, 10, "建单日期", cellXF);

            //int rowindex = 1;
            //foreach (var row in model.rows)
            //{
            //    rowindex++;
            //    cells.Add(rowindex, 1, row.IDS);
            //    cells.Add(rowindex, 2, row.TypeS);
            //    cells.Add(rowindex, 3, row.Operation);
            //    cells.Add(rowindex, 4, row.Description);
            //    cells.Add(rowindex, 5, row.Status);
            //    cells.Add(rowindex, 6, row.Priority);
            //    cells.Add(rowindex, 7, row.AssignGroup);
            //    cells.Add(rowindex, 8, row.Assigner);
            //    cells.Add(rowindex, 9, row.Submitter);
            //    cells.Add(rowindex, 10, row.CreateDate);
            //}
            //xls.Save(@"D:\download", true);
        }

        public demoModel GetSearchResultModel(string incidentid, string generalstatus, string requesttype, string requeststatus, string requestpriority, string requestbegintime, string requestendtime, string customercompany, string submittercompany, string submittergroup, string submitter, string assigneecompany, string assigneegroup, string assignee, string subject, string sortitem = null, string sortorder = null)
        {

            SetRoles(user_x);
            ViewBag.IsConfigAdmin = IsConfigAdmin;
            var USER_X = user_x.GetUser_XByName(User.Identity.Name);
            if (USER_X == null)
                throw new Exception("USER_X is null.");
            ViewBag.UserDept = USER_X.Department;
            ViewBag.UserCompany = USER_X.Company;

            var sites = user_x.GetSiteBranchByID(USER_X.ID);
            var company = USER_X.Company;

            demoModel model = new demoModel();

            string connectionStr = string.Empty;
            StringBuilder querystr = new StringBuilder();
            StringBuilder querystrIncident = new StringBuilder();
            StringBuilder querystrChange = new StringBuilder();
            StringBuilder querystrTask = new StringBuilder();
            StringBuilder querystrProblem = new StringBuilder();
            StringBuilder querystrService = new StringBuilder();
            querystrIncident = querystrIncident.Append("SELECT unique Entry_ID as RequestID, Incident_Number AS IDS,'事件' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.Submit_Date+28800,'second')as CreateDate,h.cchr_submitterfullname as  Submitter,h.Description,'' AS BDSummary,'指派' as Operation, CASE h.Status  WHEN 0 THEN '新建'  WHEN 1 THEN '已指派' WHEN 2 THEN '处理中' WHEN 3 THEN '暂停' WHEN 4 THEN '已解决' WHEN 5 THEN '已关闭' WHEN 6 THEN '已取消' ELSE '其他' END  AS Status,CASE h.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN 3 THEN '低' ELSE '其他' END  AS Priority,h.Assignee AS Assigner, h.Assigned_Group AS AssignGroup FROM aradmin.hpd_help_desk h LEFT OUTER JOIN CTM_Support_Group_Assoc_LookUp g on g.login_id=h.submitter LEFT OUTER JOIN CTM_Support_Group_Assoc_LookUp f on f.login_id=h.assignee_login_id WHERE 1=1 ");
            querystrChange = querystrChange.Append("SELECT unique Request_ID AS RequestID, h.infrastructure_change_id AS IDS,'变更' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.Submit_Date+28800,'second') as CreateDate,h.cChr_SubmitterFullName as Submitter,h.Description,'' AS BDSummary,'审批'as Operation,CASE  h.Change_Request_Status  WHEN 0 THEN case h.cDrL_BackStatus when 1 THEN '已退回' else '已提交' end  WHEN 1 THEN '已设计'  WHEN 2 THEN '变更请求' WHEN 3 THEN '进行中的计划' WHEN 7 THEN '正在执行' WHEN 8 THEN '暂停' WHEN 9 THEN '已拒绝' WHEN 10 THEN '已完成' WHEN 11 THEN '已关闭' WHEN 12 THEN '已取消' ELSE '其他' END  AS Status,CASE h.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN  3 THEN '低' ELSE '其他' END  AS Priority,h.ASCHG AS Assigner, h.ASGRP AS AssignGroup from chg_infrastructure_change h LEFT OUTER JOIN CTM_Support_Group_Assoc_LookUp g on g.login_id=h.submitter where 1=1 ");
            querystrProblem = querystrProblem.Append("SELECT unique sys_problem_investigation_id as RequestID, Problem_Investigation_ID AS IDS,'问题' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.Submit_Date+28800,'second') as CreateDate,h.cChr_SubmitterFullName as  Submitter,h.Description,'' AS BDSummary,'指派' as Operation, CASE h.Investigation_Status  WHEN 0 THEN '草稿'  WHEN 1 THEN '评审中' WHEN 2 THEN '授权请求' WHEN 3 THEN '已指派' WHEN 4 THEN '调查中' WHEN 5 THEN '暂停' WHEN 6 THEN '已完成' WHEN 7 THEN '已拒绝' WHEN 8 THEN '已关闭' WHEN 9 THEN '已取消' ELSE '其他' END  AS Status, CASE h.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN 3 THEN '低' ELSE '其他' END  AS Priority, h.Assignee AS Assigner, h.Assigned_Group AS AssignGroup FROM aradmin.pbm_problem_investigation h LEFT OUTER JOIN CTM_Support_Group_Assoc_LookUp g on g.login_id=h.submitter LEFT OUTER JOIN CTM_Support_Group_Assoc_LookUp f on f.login_id=h.assignee_login_id WHERE 1=1 ");
            querystrService = querystrService.Append("select unique ServiceRequestID AS RequestID, 'SFR0'|| h.ServiceRequestID AS IDS, '自服务' as TypeS, h.createtime as CreateDate,a.UserName AS Submitter, h.description AS Description, h.BDSummary AS BDSummary,'审批' as Operation, CASE h.Stauts WHEN 1 THEN '审批中' WHEN 2 THEN '等待中' WHEN 3 THEN '处理中' WHEN 4 THEN '已完成' WHEN 5 THEN '已拒绝' WHEN 6 THEN '已取消' ELSE '其他' END  AS Status,'' as Priority, '' AS Assigner,CASE  h.step WHEN 1 THEN '审批人' WHEN 2 THEN '主任' WHEN 3 THEN '审批人'  WHEN 4 THEN '主任'  ELSE '其他' END  AS AssignGroup  from servicerecords h LEFT OUTER JOIN accountcopy a on a.accountid=h.accountid WHERE 1=1 ");
            querystrTask = querystrTask.Append("SELECT unique Task_ID AS RequestID, h.Task_ID AS IDS,'任务' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.Create_Date+28800,'second')as CreateDate, h.cChr_SubmitterFullName as  Submitter,h.Summary AS description,'' AS BDSummary,'指派' as Operation, CASE h.Status WHEN 1000 THEN '新建' WHEN 2000 THEN '已指派'  WHEN 4000 THEN '处理中' WHEN 5000 THEN '等待' WHEN 6000 THEN '已关闭' ELSE '其他' END  AS Status, CASE h.Priority WHEN 0 THEN '严重'  WHEN 1 THEN '高' WHEN 2 THEN '中等' WHEN 3 THEN '低' ELSE '其他' END  AS Priority, h.Assignee AS Assigner, h.Assignee_Group AS AssignGroup FROM aradmin.tms_task h LEFT OUTER JOIN CTM_Support_Group_Assoc_LookUp g on g.login_id=h.submitter LEFT OUTER JOIN CTM_Support_Group_Assoc_LookUp f on f.login_id=h.assigned_To WHERE 1=1 ");


            if (!string.IsNullOrEmpty(incidentid))
            {
                if (string.IsNullOrEmpty(requesttype) || requesttype.Equals("事件"))
                {
                    requesttype = "事件";
                    querystrIncident = querystrIncident.Append(string.Format("and h.incident_number like '%{0}%' ", incidentid));
                }
                else
                {
                    return model;
                }
            }
            if (!string.IsNullOrEmpty(requestendtime))
            {
                requestendtime = DateTime.Parse(requestendtime).AddHours(24).ToString("MM/dd/yyyy HH:mm:ss");
            }
            switch (requesttype)
            {
                #region 事件
                case "事件":
                    connectionStr = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;

                    if (generalstatus.Equals("办结"))//已解决4，关闭5，暂停3
                    {
                        querystrIncident = querystrIncident.Append("and (h.status=3 or h.status =4 or h.status =5) ");
                    }
                    else if (generalstatus.Equals("未办结"))//新建0，已派发1，处理中2
                    {
                        querystrIncident = querystrIncident.Append("and (h.status=0 or h.status =1 or h.status =2) ");
                    }
                    if (!string.IsNullOrEmpty(requeststatus))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.status={0} ", requeststatus));
                    }
                    if (!string.IsNullOrEmpty(requestpriority))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.Priority={0} ", requestpriority));
                    }
                    if (!string.IsNullOrEmpty(requestbegintime))
                    {
                        querystrIncident = querystrIncident.Append(string.Format(" and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                    }
                    if (!string.IsNullOrEmpty(requestendtime))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                    }
                    if (!string.IsNullOrEmpty(customercompany))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.Contact_Company ='{0}' ", customercompany));
                    }
                    if (!string.IsNullOrEmpty(submittercompany))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.owner_support_company ='{0}' ", submittercompany));
                    }
                    if (!string.IsNullOrEmpty(submittergroup))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and g.support_group_name ='{0}' ", submittergroup));
                    }
                    if (!string.IsNullOrEmpty(submitter))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.submitter ='{0}' ", submitter));
                    }
                    if (!string.IsNullOrEmpty(assigneecompany))
                    {
                        string[] coms = assigneecompany.Split(';');
                        if (coms.Length > 1)
                        {
                            querystrIncident = querystrIncident.Append(string.Format("and (h.assigned_support_company ='{0}' or h.assigned_support_company ='{1}') ", coms[0], coms[1]));
                        }
                        else
                        {
                            querystrIncident = querystrIncident.Append(string.Format("and h.assigned_support_company ='{0}' ", assigneecompany));
                        }
                    }
                    if (!string.IsNullOrEmpty(assigneegroup))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.assigned_group ='{0}' ", assigneegroup));
                    }
                    if (!string.IsNullOrEmpty(assignee))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.assignee_login_id ='{0}' ", assignee));
                    }
                    if (!string.IsNullOrEmpty(subject))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.Description like '%{0}%' ", subject));
                    }
                    model.rows = ToDoHelper.GetSearchList(connectionStr, querystrIncident.ToString(), out model.total, sortitem, sortorder);
                    break;
                #endregion
                #region 变更
                case "变更":

                    connectionStr = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;

                    if (generalstatus.Equals("办结"))//已完成10,已关闭11,已退回（status=0&&cDrL_BackStatus=1）,已拒绝9
                    {
                        querystrChange = querystrChange.Append("and (h.Change_Request_Status=10 or h.Change_Request_Status =11 or h.Change_Request_Status =9 or (h.Change_Request_Status =0 and h.cDrL_BackStatus =1)) ");
                    }
                    else if (generalstatus.Equals("未办结"))//草稿0,变更请求2,审批中(status>2&&status<7),正在执行7,已暂停8
                    {
                        querystrChange = querystrChange.Append("and (h.Change_Request_Status=0 or h.Change_Request_Status =2 or h.Change_Request_Status =3 or h.Change_Request_Status=7 or h.Change_Request_Status =8) ");
                    }
                    if (!string.IsNullOrEmpty(requeststatus) && requeststatus.Equals("已退回"))
                    {
                        querystrChange = querystrChange.Append("and h.Change_Request_Status =0 and h.cDrL_BackStatus =1 ");
                    }
                    else if (!string.IsNullOrEmpty(requeststatus) && !requeststatus.Equals("已退回"))
                    {
                        querystrChange = querystrChange.Append(string.Format("and h.Change_Request_Status={0} ", requeststatus));
                    }
                    if (!string.IsNullOrEmpty(requestpriority))
                    {
                        querystrChange = querystrChange.Append(string.Format("and h.Priority={0} ", requestpriority));
                    }
                    if (!string.IsNullOrEmpty(requestbegintime))
                    {
                        querystrChange = querystrChange.Append(string.Format(" and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                    }
                    if (!string.IsNullOrEmpty(requestendtime))
                    {
                        querystrChange = querystrChange.Append(string.Format("and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                    }
                    if (!string.IsNullOrEmpty(submittercompany))
                    {
                        querystrChange = querystrChange.Append(string.Format("and h.Company ='{0}' ", submittercompany));
                    }
                    if (!string.IsNullOrEmpty(submittergroup))
                    {
                        querystrChange = querystrChange.Append(string.Format("and g.support_group_name ='{0}' ", submittergroup));
                    }
                    if (!string.IsNullOrEmpty(submitter))
                    {
                        querystrChange = querystrChange.Append(string.Format("and h.submitter ='{0}' ", submitter));
                    }
                    if (!string.IsNullOrEmpty(assigneecompany))
                    {
                        string[] coms = assigneecompany.Split(';');
                        if (coms.Length > 1)
                        {
                            querystrChange = querystrChange.Append(string.Format("and (h.ASCPY ='{0}' or h.ASCPY ='{1}') ", coms[0], coms[1]));
                        }
                        else
                        {
                            querystrChange = querystrChange.Append(string.Format("and h.ASCPY ='{0}' ", assigneecompany));
                        }
                    }
                    if (!string.IsNullOrEmpty(assigneegroup))
                    {
                        querystrChange = querystrChange.Append(string.Format("and h.ASGRP ='{0}' ", assigneegroup));
                    }
                    if (!string.IsNullOrEmpty(assignee))
                    {
                        querystrChange = querystrChange.Append(string.Format("and h.ASCHG ='{0}' ", assignee));
                    }
                    if (!string.IsNullOrEmpty(subject))
                    {
                        querystrChange = querystrChange.Append(string.Format("and h.Description like '%{0}%' ", subject));
                    }
                    model.rows = ToDoHelper.GetSearchList(connectionStr, querystrChange.ToString(), out model.total, sortitem, sortorder);
                    break;
                #endregion
                #region 问题
                case "问题":

                    connectionStr = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;

                    if (generalstatus.Equals("办结"))//已完成6，已关闭8，已取消9
                    {
                        querystrProblem = querystrProblem.Append("and (h.Investigation_Status=6 or h.Investigation_Status =8 or h.Investigation_Status =9) ");
                    }
                    else if (generalstatus.Equals("未办结"))//草稿0，评审中1，调查中4，待决5
                    {
                        querystrProblem = querystrProblem.Append("and (h.Investigation_Status=0 or h.Investigation_Status =1 or h.Investigation_Status =4 or h.Investigation_Status =5) ");
                    }
                    if (!string.IsNullOrEmpty(requeststatus))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and h.Investigation_Status={0} ", requeststatus));
                    }
                    if (!string.IsNullOrEmpty(requestpriority))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and h.Priority={0} ", requestpriority));
                    }
                    if (!string.IsNullOrEmpty(requestbegintime))
                    {
                        querystrProblem = querystrProblem.Append(string.Format(" and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                    }
                    if (!string.IsNullOrEmpty(requestendtime))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                    }
                    if (!string.IsNullOrEmpty(submittercompany))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and h.Company ='{0}' ", submittercompany));
                    }
                    if (!string.IsNullOrEmpty(submittergroup))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and g.support_group_name ='{0}' ", submittergroup));
                    }
                    if (!string.IsNullOrEmpty(submitter))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and h.submitter ='{0}' ", submitter));
                    }
                    if (!string.IsNullOrEmpty(assigneecompany))
                    {
                        string[] coms = assigneecompany.Split(';');
                        if (coms.Length > 1)
                        {
                            querystrProblem = querystrProblem.Append(string.Format("and (h.assigned_support_company ='{0}' or h.assigned_support_company ='{1}') ", coms[0], coms[1]));
                        }
                        else
                        {
                            querystrProblem = querystrProblem.Append(string.Format("and h.assigned_support_company ='{0}' ", assigneecompany));
                        }

                    }
                    if (!string.IsNullOrEmpty(assigneegroup))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and h.assigned_group ='{0}' ", assigneegroup));
                    }
                    if (!string.IsNullOrEmpty(assignee))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and h.assignee_login_id ='{0}' ", assignee));
                    }
                    if (!string.IsNullOrEmpty(subject))
                    {
                        querystrProblem = querystrProblem.Append(string.Format("and h.Description like '%{0}%' ", subject));
                    }
                    model.rows = ToDoHelper.GetSearchList(connectionStr, querystrProblem.ToString(), out model.total, sortitem, sortorder);
                    break;
                #endregion
                #region 任务
                case "任务":

                    connectionStr = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;

                    if (generalstatus.Equals("办结"))//已完成6000
                    {
                        querystrTask = querystrTask.Append("and (h.status=6000) ");
                    }
                    else if (generalstatus.Equals("未办结"))//新建1000，已指派2000，处理中4000，等待5000
                    {
                        querystrTask = querystrTask.Append("and (h.status=1000 or h.status =2000 or h.status =4000 or h.status =5000) ");
                    }
                    if (!string.IsNullOrEmpty(requeststatus))
                    {
                        querystrTask = querystrTask.Append(string.Format("and h.status={0} ", requeststatus));
                    }
                    if (!string.IsNullOrEmpty(requestpriority))
                    {
                        querystrTask = querystrTask.Append(string.Format("and h.Priority={0} ", requestpriority));
                    }
                    if (!string.IsNullOrEmpty(requestbegintime))
                    {
                        querystrTask = querystrTask.Append(string.Format(" and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.Create_Date+28800,'second') >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                    }
                    if (!string.IsNullOrEmpty(requestendtime))
                    {
                        querystrTask = querystrTask.Append(string.Format("and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.Create_Date+28800,'second') <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                    }
                    if (!string.IsNullOrEmpty(submittercompany))
                    {
                        querystrTask = querystrTask.Append(string.Format("and h.Company ='{0}' ", submittercompany));
                    }
                    if (!string.IsNullOrEmpty(submittergroup))
                    {
                        querystrTask = querystrTask.Append(string.Format("and g.support_group_name ='{0}' ", submittergroup));
                    }
                    if (!string.IsNullOrEmpty(submitter))
                    {
                        querystrTask = querystrTask.Append(string.Format("and h.submitter ='{0}' ", submitter));
                    }
                    if (!string.IsNullOrEmpty(assigneecompany))
                    {
                        string[] coms = assigneecompany.Split(';');
                        if (coms.Length > 1)
                        {
                            querystrTask = querystrTask.Append(string.Format("and (h.support_company ='{0}' or h.support_company ='{1}') ", coms[0], coms[1]));
                        }
                        else
                        {
                            querystrTask = querystrTask.Append(string.Format("and h.support_company ='{0}' ", assigneecompany));
                        }
                    }
                    if (!string.IsNullOrEmpty(assigneegroup))
                    {
                        querystrTask = querystrTask.Append(string.Format("and h.assignee_group ='{0}' ", assigneegroup));
                    }
                    if (!string.IsNullOrEmpty(assignee))
                    {
                        querystrTask = querystrTask.Append(string.Format("and h.assigned_To ='{0}' ", assignee));
                    }
                    if (!string.IsNullOrEmpty(subject))
                    {
                        querystrTask = querystrTask.Append(string.Format("and h.Summary like '%{0}%' ", subject));
                    }
                    model.rows = ToDoHelper.GetSearchList(connectionStr, querystrTask.ToString(), out model.total, sortitem, sortorder);
                    break;
                #endregion
                #region 自服务
                case "自服务":

                    connectionStr = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
                    if (generalstatus.Equals("办结"))//已完成4，已拒绝5，已取消6
                    {
                        querystrService = querystrService.Append("and (h.Stauts=4 or h.Stauts=5 or h.Stauts=6) ");
                    }
                    else if (generalstatus.Equals("未办结"))//审批中1，等待中2，处理中3
                    {
                        querystrService = querystrService.Append("and (h.Stauts=1 or h.Stauts=2 or h.Stauts=3) ");
                    }
                    if (!string.IsNullOrEmpty(requeststatus))
                    {
                        querystrService = querystrService.Append(string.Format("and h.stauts={0} ", requeststatus));
                    }
                    if (!string.IsNullOrEmpty(requestbegintime))
                    {
                        querystrService = querystrService.Append(string.Format(" and (h.createtime >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                    }
                    if (!string.IsNullOrEmpty(requestendtime))
                    {
                        querystrService = querystrService.Append(string.Format("and (h.createtime <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                    }
                    if (!string.IsNullOrEmpty(submittercompany))
                    {
                        querystrService = querystrService.Append(string.Format("and a.Company ='{0}' ", submittercompany));
                    }
                    if (!string.IsNullOrEmpty(submitter))
                    {
                        querystrService = querystrService.Append(string.Format("and h.accountid ='{0}' ", submitter));
                    }
                    if (!string.IsNullOrEmpty(subject))
                    {
                        querystrService = querystrService.Append(string.Format("and h.description like '%{0}%' ", subject));
                    }
                    model.rows = ToDoHelper.GetSearchList(connectionStr, querystrService.ToString(), out model.total, sortitem, sortorder);
                    break;
                #endregion
                #region 综合
                default:
                    if (generalstatus == "办结")//已解决4，关闭5，暂停3
                    {
                        querystrIncident = querystrIncident.Append("and (h.status=3 or h.status =4 or h.status =5) ");
                        querystrChange = querystrChange.Append("and (h.Change_Request_Status=10 or h.Change_Request_Status =11 or h.Change_Request_Status =9 or (h.Change_Request_Status =0 and h.cDrL_BackStatus =1)) ");
                        querystrProblem = querystrProblem.Append("and (h.Investigation_Status=6 or h.Investigation_Status =8 or h.Investigation_Status =9) ");
                        querystrTask = querystrTask.Append("and (h.status=6000) ");
                        querystrService = querystrService.Append("and (h.Stauts=4 or h.Stauts=5 or h.Stauts=6) ");

                    }
                    else if (generalstatus == "未办结")//新建0，已派发1，处理中2
                    {
                        querystrIncident = querystrIncident.Append("and (h.status=0 or h.status =1 or h.status =2) ");
                        querystrChange = querystrChange.Append("and (h.Change_Request_Status=0 or h.Change_Request_Status =2 or h.Change_Request_Status =3 or h.Change_Request_Status=7 or h.Change_Request_Status =8) ");
                        querystrProblem = querystrProblem.Append("and (h.Investigation_Status=0 or h.Investigation_Status =1 or h.Investigation_Status =4 or h.Investigation_Status =5) ");
                        querystrTask = querystrTask.Append("and (h.status=1000 or h.status =2000 or h.status =4000 or h.status =5000) ");
                        querystrService = querystrService.Append("and (h.Stauts=1 or h.Stauts=2 or h.Stauts=3) ");
                    }
                    if (!string.IsNullOrEmpty(requestpriority))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.Priority={0} ", requestpriority));
                        querystrChange = querystrChange.Append(string.Format("and h.Priority={0} ", requestpriority));
                        querystrProblem = querystrProblem.Append(string.Format("and h.Priority={0} ", requestpriority));
                        querystrTask = querystrTask.Append(string.Format("and h.Priority={0} ", requestpriority));
                        querystrService = querystrService.Append("and 1=2 ");
                    }
                    if (!string.IsNullOrEmpty(requestbegintime))
                    {
                        querystrIncident = querystrIncident.Append(string.Format(" and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                        querystrChange = querystrChange.Append(string.Format(" and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                        querystrProblem = querystrProblem.Append(string.Format(" and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                        querystrTask = querystrTask.Append(string.Format(" and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.Create_Date+28800,'second') >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                        querystrService = querystrService.Append(string.Format(" and (h.createtime >=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestbegintime));
                    }
                    if (!string.IsNullOrEmpty(requestendtime))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                        querystrChange = querystrChange.Append(string.Format("and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                        querystrProblem = querystrProblem.Append(string.Format("and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.submit_date+28800,'second') <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                        querystrTask = querystrTask.Append(string.Format("and (to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(h.Create_Date+28800,'second') <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                        querystrService = querystrService.Append(string.Format("and (h.createtime <=to_date('{0}','mm/dd/yyyy hh24:mi:ss')) ", requestendtime));
                    }
                    if (!string.IsNullOrEmpty(customercompany))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.Contact_Company ='{0}' ", customercompany));
                        querystrChange = querystrChange.Append("and 1=1 ");
                        querystrProblem = querystrProblem.Append("and 1=1 ");
                        querystrTask = querystrTask.Append("and 1=1 ");
                        if (USER_X.Department != "中软海晟技术支持组")
                        {
                            querystrService = querystrService.Append("and 1=1 ");
                        }
                    }
                    if (!string.IsNullOrEmpty(submittercompany))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.owner_support_company ='{0}' ", submittercompany));
                        querystrChange = querystrChange.Append(string.Format("and h.Company ='{0}' ", submittercompany));
                        querystrProblem = querystrProblem.Append(string.Format("and h.Company ='{0}' ", submittercompany));
                        querystrTask = querystrTask.Append(string.Format("and h.Company ='{0}' ", submittercompany));
                        querystrService = querystrService.Append(string.Format("and a.Company ='{0}' ", submittercompany));
                    }
                    if (!string.IsNullOrEmpty(submittergroup))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and g.support_group_name ='{0}' ", submittergroup));
                        querystrChange = querystrChange.Append(string.Format("and g.support_group_name ='{0}' ", submittergroup));
                        querystrProblem = querystrProblem.Append(string.Format("and g.support_group_name ='{0}' ", submittergroup));
                        querystrTask = querystrTask.Append(string.Format("and g.support_group_name ='{0}' ", submittergroup));
                    }
                    if (!string.IsNullOrEmpty(submitter))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.submitter ='{0}' ", submitter));
                        querystrChange = querystrChange.Append(string.Format("and h.submitter ='{0}' ", submitter));
                        querystrProblem = querystrProblem.Append(string.Format("and h.submitter ='{0}' ", submitter));
                        querystrTask = querystrTask.Append(string.Format("and h.submitter ='{0}' ", submitter));
                        querystrService = querystrService.Append(string.Format("and h.accountid ='{0}' ", submitter));
                    }
                    if (!string.IsNullOrEmpty(assigneecompany))
                    {
                        string[] coms = assigneecompany.Split(';');
                        if (coms.Length > 1)
                        {
                            querystrIncident = querystrIncident.Append(string.Format("and (h.assigned_support_company ='{0}' or h.assigned_support_company ='{1}') ", coms[0], coms[1]));
                            querystrChange = querystrChange.Append(string.Format("and (h.ASCPY ='{0}' or h.ASCPY ='{1}') ", coms[0], coms[1]));
                            querystrTask = querystrTask.Append(string.Format("and (h.support_company ='{0}' or h.support_company ='{1}') ", coms[0], coms[1]));
                            querystrProblem = querystrProblem.Append(string.Format("and (h.assigned_support_company ='{0}' or h.assigned_support_company ='{1}') ", coms[0], coms[1]));
                        }
                        else
                        {
                            querystrIncident = querystrIncident.Append(string.Format("and h.assigned_support_company ='{0}' ", assigneecompany));
                            querystrChange = querystrChange.Append(string.Format("and h.ASCPY ='{0}' ", assigneecompany));
                            querystrTask = querystrTask.Append(string.Format("and h.support_company ='{0}' ", assigneecompany));
                            querystrProblem = querystrProblem.Append(string.Format("and h.assigned_support_company ='{0}' ", assigneecompany));
                        }
                        querystrService = querystrService.Append("and 1=2 ");
                    }
                    if (!string.IsNullOrEmpty(assigneegroup))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.assigned_group ='{0}' ", assigneegroup));
                        querystrTask = querystrTask.Append(string.Format("and h.assignee_group ='{0}' ", assigneegroup));
                        querystrProblem = querystrProblem.Append(string.Format("and h.assigned_group ='{0}' ", assigneegroup));
                        querystrChange = querystrChange.Append(string.Format("and h.ASGRP ='{0}' ", assigneegroup));
                        querystrService = querystrService.Append("and 1=2 ");
                    }
                    if (!string.IsNullOrEmpty(assignee))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.assignee_login_id ='{0}' ", assignee));
                        querystrProblem = querystrProblem.Append(string.Format("and h.assignee_login_id ='{0}' ", assignee));
                        querystrTask = querystrTask.Append(string.Format("and h.assigned_To ='{0}' ", assignee));
                        querystrChange = querystrChange.Append(string.Format("and h.ASCHG ='{0}' ", assignee));
                        querystrService = querystrService.Append("and 1=2 ");
                    }
                    if (!string.IsNullOrEmpty(subject))
                    {
                        querystrIncident = querystrIncident.Append(string.Format("and h.Description like '%{0}%' ", subject));
                        querystrChange = querystrChange.Append(string.Format("and h.Description like '%{0}%' ", subject));
                        querystrProblem = querystrProblem.Append(string.Format("and h.Description like '%{0}%' ", subject));
                        querystrTask = querystrTask.Append(string.Format("and h.Summary like '%{0}%' ", subject));
                        querystrService = querystrService.Append(string.Format("and h.description like '%{0}%' ", subject));
                    }
                    querystr = querystr.Append("(").Append(querystrIncident).Append(") Union ALL (").Append(querystrChange).Append(") Union ALL (").Append(querystrProblem).Append(") Union ALL (").Append(querystrTask).Append(")");
                    model.rows = ToDoHelper.GetSearchListAll(querystr.ToString(), querystrService.ToString(), out model.total, sortitem, sortorder);
                    break;
                #endregion
            }
            return model;
        }

    }
}
